﻿using System;
using System.Data;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using DBUtility;

namespace SQLServerDAL
{
    /// <summary>
    /// 会员管理
    /// </summary>
    public class MembershipBase
    {
        private SqlParameter[] Parms;
        private SqlConnection Conn;

        public MembershipBase()
        {
            Conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);
        }

        #region 创建会员帐号 / 注册会员
        /// <summary>
        ///  创建会员帐号 / 注册会员, uniqueEmail = 1 Email 帐号唯一验证
        /// </summary>
        /// <param name="_userObj"></param>
        /// <returns></returns>
        public string CreateUser(Model.MembershipBase _userObj, bool uniqueEmail)
        {
            Parms = new SqlParameter[] {
					new SqlParameter("@UserName", SqlDbType.NVarChar,256),
					new SqlParameter("@Password", SqlDbType.NVarChar,128),
					new SqlParameter("@PasswordSalt", SqlDbType.NVarChar,256),
					new SqlParameter("@Email", SqlDbType.NVarChar,256),
					new SqlParameter("@MobileAlias", SqlDbType.NVarChar, 256),
					new SqlParameter("@Birthday", SqlDbType.DateTime),
					new SqlParameter("@FullName", SqlDbType.NVarChar, 256),
					new SqlParameter("@PasswordQuestion", SqlDbType.NVarChar, 256),
					new SqlParameter("@PasswordAnswer", SqlDbType.NVarChar, 256),
                    new SqlParameter("@IsApproved",SqlDbType.Bit),
                    new SqlParameter("@IsLockedOut",SqlDbType.Bit),
					new SqlParameter("@MobilePIN", SqlDbType.VarChar, 256),
					new SqlParameter("@Comment", SqlDbType.NVarChar, 256),
					new SqlParameter("@RegionId", SqlDbType.Int),
					new SqlParameter("@CountryId", SqlDbType.Int),
					new SqlParameter("@ProvinceId", SqlDbType.Int),
					new SqlParameter("@CityId", SqlDbType.Int),
					new SqlParameter("@CountieId", SqlDbType.Int),
                    new SqlParameter("@RegionInfo",SqlDbType.NVarChar, 512),
                    new SqlParameter("@CreateDate",SqlDbType.DateTime),
					new SqlParameter("@UniqueEmail", SqlDbType.Int),
					new SqlParameter("@PasswordFormat", SqlDbType.Int), 
					new SqlParameter("@Gender", SqlDbType.Int), 
					new SqlParameter("@Address", SqlDbType.NVarChar, 512),
					new SqlParameter("@Tel", SqlDbType.NVarChar, 256),
					new SqlParameter("@ZIP", SqlDbType.NVarChar, 256),
					new SqlParameter("@Mobile", SqlDbType.NVarChar, 256),
                    new SqlParameter("@UserId",SqlDbType.UniqueIdentifier, 16, "Output"),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };
            Parms[0].Value = _userObj.UserName;
            Parms[1].Value = _userObj.Password;
            Parms[2].Value = _userObj.PasswordSalt;
            Parms[3].Value = _userObj.Email;
            Parms[4].Value = _userObj.MobileAlias;
            Parms[5].Value = _userObj.Birthday;
            Parms[6].Value = _userObj.FullName;
            Parms[7].Value = _userObj.PasswordQuestion;
            Parms[8].Value = _userObj.PasswordAnswer;
            Parms[9].Value = _userObj.IsApproved;
            Parms[10].Value = _userObj.IsLockedOut;
            Parms[11].Value = _userObj.MobilePIN??string.Empty;
            Parms[12].Value = _userObj.Comment;
            Parms[13].Value = _userObj.RegionId;
            Parms[14].Value = _userObj.CountryId;
            Parms[15].Value = _userObj.ProvinceId;
            Parms[16].Value = _userObj.CityId;
            Parms[17].Value = _userObj.CountieId;
            Parms[18].Value = _userObj.RegionInfo;
            Parms[19].Value = _userObj.CreateDate;
            Parms[20].Value = uniqueEmail ? 1 : 0;
            Parms[21].Value = _userObj.PasswordFormat;
            Parms[22].Value = _userObj.Gender;
            Parms[23].Value = _userObj.Address;
            Parms[24].Value = _userObj.Tel;
            Parms[25].Value = _userObj.ZIP;
            Parms[26].Value = _userObj.Mobile;
            Parms[27].Direction = ParameterDirection.Output;
            Parms[28].Direction = ParameterDirection.ReturnValue;

            string result = "";
            int status = 0;
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "Membership_CreateUser", Parms);
                status = ((Parms[28].Value != null) ? ((int)Parms[28].Value) : -1);
                if (status == 0)
                {
                    result = "succeeded";
                    if (Common.Utils.IsGuid(Parms[27].Value.ToString()))
                        _userObj.UserId = Parms[27].Value.ToString();

                    result += "|" + _userObj.UserId;
                }
                else
                {
                    result = status.ToString(); ///exists;
                } 
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
            return result;
        }
        #endregion

        #region 修改会员帐号信息
        /// <summary>
        ///  修修改会员帐号信息, uniqueEmail = 1 Email 帐号唯一验证
        /// </summary>
        /// <param name="_userObj"></param>
        /// <returns></returns>
        public string UpdateUser(Model.MembershipBase _userObj, bool uniqueEmail)
        {
            Parms = new SqlParameter[] {
					new SqlParameter("@UserName", SqlDbType.NVarChar,256),
					new SqlParameter("@Email", SqlDbType.NVarChar,256),
					new SqlParameter("@MobileAlias", SqlDbType.NVarChar, 256),
					new SqlParameter("@Birthday", SqlDbType.DateTime),
					new SqlParameter("@FullName", SqlDbType.NVarChar, 256),
					new SqlParameter("@PasswordQuestion", SqlDbType.NVarChar, 256),
					new SqlParameter("@PasswordAnswer", SqlDbType.NVarChar, 256),
                    new SqlParameter("@IsApproved",SqlDbType.Bit),
                    new SqlParameter("@IsLockedOut",SqlDbType.Bit),
					new SqlParameter("@MobilePIN", SqlDbType.VarChar, 256),
					new SqlParameter("@Comment", SqlDbType.NVarChar, 256),
					new SqlParameter("@RegionId", SqlDbType.Int),
					new SqlParameter("@CountryId", SqlDbType.Int),
					new SqlParameter("@ProvinceId", SqlDbType.Int),
					new SqlParameter("@CityId", SqlDbType.Int),
					new SqlParameter("@CountieId", SqlDbType.Int),
                    new SqlParameter("@RegionInfo",SqlDbType.NVarChar, 512), 
					new SqlParameter("@UniqueEmail", SqlDbType.Int), 
					new SqlParameter("@Gender", SqlDbType.Int), 
					new SqlParameter("@Address", SqlDbType.NVarChar, 512),
					new SqlParameter("@Tel", SqlDbType.NVarChar, 256),
					new SqlParameter("@ZIP", SqlDbType.NVarChar, 256),
					new SqlParameter("@Mobile", SqlDbType.NVarChar, 256),
                    new SqlParameter("@UserId",SqlDbType.UniqueIdentifier),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };
            Parms[0].Value = _userObj.UserName;
            Parms[1].Value = _userObj.Email;
            Parms[2].Value = _userObj.MobileAlias;
            Parms[3].Value = _userObj.Birthday;
            Parms[4].Value = _userObj.FullName;
            Parms[5].Value = _userObj.PasswordQuestion;
            Parms[6].Value = _userObj.PasswordAnswer;
            Parms[7].Value = _userObj.IsApproved;
            Parms[8].Value = _userObj.IsLockedOut;
            Parms[9].Value = _userObj.MobilePIN??string.Empty;
            Parms[10].Value = _userObj.Comment;
            Parms[11].Value = _userObj.RegionId;
            Parms[12].Value = _userObj.CountryId;
            Parms[13].Value = _userObj.ProvinceId;
            Parms[14].Value = _userObj.CityId;
            Parms[15].Value = _userObj.CountieId;
            Parms[16].Value = _userObj.RegionInfo; 
            Parms[17].Value = uniqueEmail ? 1 : 0;
            Parms[18].Value = _userObj.Gender;
            Parms[19].Value = _userObj.Address;
            Parms[20].Value = _userObj.Tel;
            Parms[21].Value = _userObj.ZIP;
            Parms[22].Value = _userObj.Mobile;
            Parms[23].Value = new Guid(_userObj.UserId);
            Parms[24].Direction = ParameterDirection.ReturnValue;

            string result = "";
            int status = 0;
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "Membership_UpdateUser", Parms);
                status = ((Parms[24].Value != null) ? ((int)Parms[24].Value) : -1);
                if (status == 0)
                {
                    result = "succeeded"; 
                    result += "|" + _userObj.UserId;
                }
                else
                {
                    result = status.ToString(); ///exists;
                }
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
            return result;
        }
        #endregion

        #region 查询会员信息
        /// <summary>
        /// 会员产品信息实体
        /// </summary>
        /// <param name="productId"></param>
        /// <returns></returns>
        public Model.MembershipBase GetObjById(string userId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM Membership WHERE UserId = @UserId AND DeletionStateCode = 0");

            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.UniqueIdentifier) };
            parameters[0].Value = new Guid(userId);

            Model.MembershipBase _userObj = null;
            try
            {
                SqlDataReader rd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
                if (rd.Read())
                {
                    _userObj = new Model.MembershipBase();
                    _userObj.UserName = rd["UserName"].ToString();
                    _userObj.LoweredUserName = rd["LoweredUserName"].ToString();
                    _userObj.FullName = rd["FullName"].ToString();
                    _userObj.MobileAlias = rd["MobileAlias"].ToString();
                    _userObj.IsAnonymous = bool.Parse(rd["IsAnonymous"].ToString());
                    _userObj.LastActivityDate = DateTime.Parse(rd["LastActivityDate"].ToString());
                    _userObj.Password = rd["Password"].ToString();
                    _userObj.PasswordFormat = int.Parse(rd["PasswordFormat"].ToString()); 
                    _userObj.PasswordSalt = rd["PasswordSalt"].ToString();
                    _userObj.MobilePIN = rd["MobilePIN"].ToString();
                    _userObj.Email = rd["Email"].ToString();
                    _userObj.LoweredEmail = rd["LoweredEmail"].ToString();
                    _userObj.PasswordQuestion = rd["PasswordQuestion"].ToString();
                    _userObj.PasswordAnswer = rd["PasswordAnswer"].ToString();
                    _userObj.IsApproved = bool.Parse(rd["IsApproved"].ToString());
                    _userObj.IsLockedOut = bool.Parse(rd["IsLockedOut"].ToString());
                    _userObj.CreateDate = DateTime.Parse(rd["CreateDate"].ToString());
                    _userObj.LastLoginDate = DateTime.Parse(rd["LastLoginDate"].ToString());
                    _userObj.LastPasswordChangedDate = DateTime.Parse(rd["LastPasswordChangedDate"].ToString());
                    _userObj.LastLockoutDate = DateTime.Parse(rd["LastLockoutDate"].ToString());
                    _userObj.FailedPasswordAttemptCount = int.Parse(rd["FailedPasswordAttemptCount"].ToString());
                    _userObj.FailedPasswordAttemptWindowStart = DateTime.Parse(rd["FailedPasswordAttemptWindowStart"].ToString());
                    _userObj.FailedPasswordAnswerAttemptCount = int.Parse(rd["FailedPasswordAnswerAttemptCount"].ToString());
                    _userObj.FailedPasswordAnswerAttemptWindowStart = DateTime.Parse(rd["FailedPasswordAnswerAttemptWindowStart"].ToString());
                    _userObj.Comment = rd["Comment"].ToString();
                    _userObj.RegionId = int.Parse(rd["RegionId"].ToString());
                    _userObj.CountryId = int.Parse(rd["CountryId"].ToString());
                    _userObj.ProvinceId = int.Parse(rd["ProvinceId"].ToString());
                    _userObj.CityId = int.Parse(rd["CityId"].ToString());
                    _userObj.CountieId = int.Parse(rd["CountieId"].ToString());
                    _userObj.RegionInfo = rd["RegionInfo"].ToString();
                    _userObj.Gender = int.Parse(rd["Gender"].ToString());
                    _userObj.Birthday = DateTime.Parse(rd["Birthday"].ToString());
                    _userObj.Address = rd["Address"].ToString();
                    _userObj.Tel = rd["Tel"].ToString();
                    _userObj.ZIP = rd["ZIP"].ToString();
                    _userObj.Mobile = rd["Mobile"].ToString();
                    _userObj.UserIntegral = string.IsNullOrEmpty(rd["IntegralAmount"].ToString()) ? 0 : Convert.ToInt32(rd["IntegralAmount"].ToString());

                }
            }
            catch
            {
                return null;
            }
            return _userObj;
        }
        /// <summary>
        /// 会员产品信息实体
        /// </summary>
        /// <param name="productId"></param>
        /// <returns></returns>
        public Model.MembershipBase GetObjByUsername(string userName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM Membership WHERE LoweredUserName = Lower(@UserName) AND DeletionStateCode = 0 ");

            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@UserName", SqlDbType.VarChar, 256) };
            parameters[0].Value = userName;

            Model.MembershipBase _userObj = null;
            try
            {
                SqlDataReader rd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
                if (rd.Read())
                {
                    _userObj = new Model.MembershipBase();
                    _userObj.UserId = rd["UserId"].ToString();
                    _userObj.UserName = rd["UserName"].ToString();
                    _userObj.LoweredUserName = rd["LoweredUserName"].ToString();
                    _userObj.FullName = rd["FullName"].ToString();
                    _userObj.MobileAlias = rd["MobileAlias"].ToString();
                    _userObj.IsAnonymous = bool.Parse(rd["IsAnonymous"].ToString());
                    _userObj.LastActivityDate = DateTime.Parse(rd["LastActivityDate"].ToString());
                    _userObj.Password = rd["Password"].ToString();
                    _userObj.PasswordFormat = int.Parse(rd["PasswordFormat"].ToString());
                    _userObj.PasswordSalt = rd["PasswordSalt"].ToString();
                    _userObj.MobilePIN = rd["MobilePIN"].ToString();
                    _userObj.Email = rd["Email"].ToString();
                    _userObj.LoweredEmail = rd["LoweredEmail"].ToString();
                    _userObj.PasswordQuestion = rd["PasswordQuestion"].ToString();
                    _userObj.PasswordAnswer = rd["PasswordAnswer"].ToString();
                    _userObj.IsApproved = bool.Parse(rd["IsApproved"].ToString());
                    _userObj.IsLockedOut = bool.Parse(rd["IsLockedOut"].ToString());
                    _userObj.CreateDate = DateTime.Parse(rd["CreateDate"].ToString());
                    _userObj.Comment = rd["Comment"].ToString();
                    _userObj.RegionId = int.Parse(rd["RegionId"].ToString());
                    _userObj.CountryId = int.Parse(rd["CountryId"].ToString());
                    _userObj.ProvinceId = int.Parse(rd["ProvinceId"].ToString());
                    _userObj.CityId = int.Parse(rd["CityId"].ToString());
                    _userObj.CountieId = int.Parse(rd["CountieId"].ToString());
                    _userObj.RegionInfo = rd["RegionInfo"].ToString();
                    _userObj.Gender = int.Parse(rd["Gender"].ToString());
                    _userObj.Birthday = DateTime.Parse(rd["Birthday"].ToString()); 
                    _userObj.Address = rd["Address"].ToString();
                    _userObj.Tel = rd["Tel"].ToString();
                    _userObj.ZIP = rd["ZIP"].ToString();
                    _userObj.Mobile = rd["Mobile"].ToString();
                    _userObj.UserIntegral = string.IsNullOrEmpty(rd["IntegralAmount"].ToString()) ? 0 : Convert.ToInt32(rd["IntegralAmount"].ToString());
                }
            }
            catch
            {
                return null;
            }
            return _userObj;
        }
        #endregion

        #region 删除会员帐号
        /// <summary>
        /// 删除会员帐号
        /// </summary> 
        /// <returns></returns>
        public bool DeleteUser(string username)
        {
            try
            {
                if (Conn.State.ToString() != "Open")
                    Conn.Open();

                SqlCommand cmd = new SqlCommand("dbo.Membership_DeleteUser", Conn);
 
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(SqlHelper.CreateInputParam("@UserName", SqlDbType.NVarChar, username));

                SqlParameter p = new SqlParameter("@ReturnValue", SqlDbType.Int);
                p.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(p);

                cmd.ExecuteNonQuery();

                int status = ((p.Value != null) ? ((int)p.Value) : -1);

                return (status == 0);
            }
            catch (Exception err)
            {
                return false;
            }
            finally
            {
                if (Conn != null)
                {
                    Conn.Dispose();
                }
            }
        }
        #endregion

        #region 会员登录验证

        /// <summary>
        /// 验证用户密码
        /// </summary>
        /// <param name="username"></param>
        /// <param name="isPasswordCorrect"></param>
        /// <param name="updateLastLoginActivityDate"></param>
        /// <param name="failIfNotApproved"></param>
        /// <param name="lastLoginDate"></param>
        /// <param name="lastActivityDate"></param>
        /// <returns></returns>
        public int UpdateMemberInfo(string username, bool isPasswordCorrect, bool updateLastLoginActivityDate, bool failIfNotApproved, DateTime lastLoginDate, DateTime lastActivityDate)
        {
            int status = -1;
            try
            {
                try
                {
                    if (Conn.State.ToString() != "Open")
                        Conn.Open();

                    SqlCommand cmd = new SqlCommand("dbo.Membership_UpdateUserInfo", Conn);
                    DateTime dtNow = DateTime.UtcNow;
 
                    cmd.CommandType = CommandType.StoredProcedure; 
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@UserName", SqlDbType.NVarChar, username));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@IsPasswordCorrect", SqlDbType.Bit, isPasswordCorrect));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@UpdateLastLoginActivityDate", SqlDbType.Bit, updateLastLoginActivityDate));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@MaxInvalidPasswordAttempts", SqlDbType.Int, 10));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@PasswordAttemptWindow", SqlDbType.Int, 10));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@CurrentTimeUtc", SqlDbType.DateTime, dtNow));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@LastLoginDate", SqlDbType.DateTime, isPasswordCorrect ? dtNow : lastLoginDate));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@LastActivityDate", SqlDbType.DateTime, isPasswordCorrect ? dtNow : lastActivityDate));

                    SqlParameter p = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    p.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);

                    cmd.ExecuteNonQuery();

                    status = ((p.Value != null) ? ((int)p.Value) : -1);
                }
                finally
                {
                    if (Conn != null)
                    {
                        Conn.Dispose();
                    }
                }
            }
            catch
            {
                throw;
            }

            return status;
        }

        /// <summary>
        /// 获取帐号密码数据
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="updateLastLoginActivityDate">是否更新活动时间</param>
        /// <param name="status"></param>
        /// <param name="password"></param>
        /// <param name="passwordFormat"></param>
        /// <param name="passwordSalt"></param>
        /// <param name="failedPasswordAttemptCount"></param>
        /// <param name="failedPasswordAnswerAttemptCount"></param>
        /// <param name="isApproved"></param>
        /// <param name="lastLoginDate"></param>
        /// <param name="lastActivityDate"></param>
        public void GetPasswordWithFormat(string username,
                                    bool updateLastLoginActivityDate,
                                    out int status,
                                    out string password,
                                    out int passwordFormat,
                                    out string passwordSalt,
                                    out int failedPasswordAttemptCount,
                                    out int failedPasswordAnswerAttemptCount,
                                    out bool isApproved,
                                    out DateTime lastLoginDate,
                                    out DateTime lastActivityDate)
        {
            try
            { 
                SqlDataReader reader = null;
                SqlParameter p = null;

                try
                {
                    if (Conn.State.ToString() != "Open")
                        Conn.Open();

                    SqlCommand cmd = new SqlCommand("dbo.Membership_GetPasswordWithFormat", Conn);
 
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@UserName", SqlDbType.NVarChar, username));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@UpdateLastLoginActivityDate", SqlDbType.Bit, updateLastLoginActivityDate));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));

                    p = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    p.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);

                    reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                    status = -1;

                    if (reader.Read())
                    {
                        password = reader.GetString(0);
                        passwordFormat = reader.GetInt32(1);
                        passwordSalt = reader.GetString(2);
                        failedPasswordAttemptCount = reader.GetInt32(3);
                        failedPasswordAnswerAttemptCount = reader.GetInt32(4);
                        isApproved = reader.GetBoolean(5);
                        lastLoginDate = reader.GetDateTime(6);
                        lastActivityDate = reader.GetDateTime(7);
                    }
                    else
                    {
                        password = null;
                        passwordFormat = 0;
                        passwordSalt = null;
                        failedPasswordAttemptCount = 0;
                        failedPasswordAnswerAttemptCount = 0;
                        isApproved = false;
                        lastLoginDate = DateTime.UtcNow;
                        lastActivityDate = DateTime.UtcNow;
                    }
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                        reader = null;

                        status = ((p.Value != null) ? ((int)p.Value) : -1);
                    }

                    if (Conn != null)
                    {
                        Conn.Close(); 
                    }
                }
            }
            catch
            {
                throw;
            }

        }
        #endregion

        #region 验证唯一 
        /// <summary>
        /// 验证E-Mail是否唯一, true 已存在, false 唯一
        /// </summary>
        public bool ValidateEmailExist(string email)
        {
            SqlParameter[] prams = {
				SqlHelper.CreateInputParam("@Email", SqlDbType.VarChar, email)
			};

            StringBuilder SQL = new StringBuilder();
            SQL.Append("SELECT COUNT(1) FROM Membership WHERE [LoweredEmail] = Lower(@Email) AND DeletionStateCode = 0 ");

            object _obj = SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringMembership, CommandType.Text, SQL.ToString(), prams);

            if (_obj != null)
            {
                return ((int)_obj > 0) ? true : false;
            }

            return false;
        }
        /// <summary>
        /// 验证Username是否唯一, true 已存在, false 唯一
        /// </summary>
        public bool ValidateUsernameExist(string username)
        {
            SqlParameter[] prams = {
				SqlHelper.CreateInputParam("@Username", SqlDbType.VarChar, username)
			};

            StringBuilder SQL = new StringBuilder();
            SQL.Append("SELECT COUNT(1) FROM Membership WHERE [LoweredUserName] = Lower(@Username) AND DeletionStateCode = 0 ");

            object _obj = SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringMembership, CommandType.Text, SQL.ToString(), prams);

            if (_obj != null)
            {
                return ((int)_obj > 0) ? true : false;
            }

            return false;
        }
        #endregion

        #region 找回密码
        /// <summary>
        /// 从数据库中找回用户密码
        /// </summary>
        /// <param name="username"></param>
        /// <param name="passwordAnswer"></param>
        /// <param name="requiresQuestionAndAnswer"></param>
        /// <param name="passwordFormat"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        private string GetPasswordFromDB(string username,
                                          string passwordAnswer,
                                          bool requiresQuestionAndAnswer,
                                          out int passwordFormat,
                                          out int status)
        {
            try
            { 
                SqlDataReader reader = null;
                SqlParameter p = null;

                try
                {

                    SqlCommand cmd = new SqlCommand("dbo.Membership_GetPassword", Conn);
 
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@UserName", SqlDbType.NVarChar, username));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@MaxInvalidPasswordAttempts", SqlDbType.Int, 10));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@PasswordAttemptWindow", SqlDbType.Int, 10));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));

                    if (requiresQuestionAndAnswer)
                    {
                        cmd.Parameters.Add(SqlHelper.CreateInputParam("@PasswordAnswer", SqlDbType.NVarChar, passwordAnswer));
                    }

                    p = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    p.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);

                    reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                    string password = null;

                    status = -1;

                    if (reader.Read())
                    {
                        password = reader.GetString(0);
                        passwordFormat = reader.GetInt32(1);
                    }
                    else
                    {
                        password = null;
                        passwordFormat = 0;
                    }

                    return password;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                        reader = null;

                        status = ((p.Value != null) ? ((int)p.Value) : -1);
                    }

                    if (Conn != null)
                    {
                        Conn.Close();
                        Conn = null;
                    }
                }
            }
            catch
            {
                throw;
            }

        }
        #endregion

        #region 修改密码
        /// <summary>
        /// 重值密码
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <param name="passwordFormat"></param>
        /// <param name="salt"></param>
        /// <returns></returns>
        public int UpdatePassword(string username, string password, int passwordFormat, int _PasswordAttemptWindow, int _MaxInvalidPasswordAttempts, string salt)
        {
            int status;

            try
            { 
                try
                {
                    if (Conn.State.ToString() != "Open")
                        Conn.Open();

                    SqlCommand cmd = new SqlCommand("dbo.Membership_ResetPassword", Conn);
 
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@UserName", SqlDbType.NVarChar, username));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@NewPassword", SqlDbType.NVarChar, password));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@PasswordSalt", SqlDbType.NVarChar, salt));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@MaxInvalidPasswordAttempts", SqlDbType.Int, _MaxInvalidPasswordAttempts));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@PasswordAttemptWindow", SqlDbType.Int, _PasswordAttemptWindow));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@PasswordFormat", SqlDbType.Int, passwordFormat));
                    cmd.Parameters.Add(SqlHelper.CreateInputParam("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));

                    SqlParameter p = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    p.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);

                    cmd.ExecuteNonQuery();

                    status = ((p.Value != null) ? ((int)p.Value) : -1);

                    return status;
                }
                finally
                {
                    if (Conn != null)
                    {
                        Conn.Dispose(); 
                    }
                }
            }
            catch
            {
                throw;
            }
        } 
        #endregion
    }
}
